package fy.input.sql;

import java.util.List;

import org.json.JSONArray;
import org.json.JSONObject;

import fy.config.FyConfig;

public class MySQLSetManager {

    private static final String OA_OPERATE = "0";
    private static final String HR_OPERATE = "1";
    private static final String UAP_OPERATE = "2";
    private static final String ERP_OPERATE = "3";
    private static List<MySQLSetBean> sqlSetList;
    private static MySQLSetManager msm;

    private MySQLSetManager() {
        sqlSetList = MySQLSetDao.querySQLSet();
    }

    public static MySQLSetManager getInstance() {
        if (msm == null) {
            synchronized (MySQLSetManager.class) {
                if (msm == null) {
                    msm = new MySQLSetManager();
                }
            }
        }
        if (sqlSetList == null) {
            sqlSetList = MySQLSetDao.querySQLSet();
        }
        return msm;
    }

    public String getSQLSetList() {
        String result = null;
        try {
            JSONObject jo = new JSONObject();
            jo.put("success", true);
            for (int i = 0, size = sqlSetList.size(); i < size; i++) {
                JSONObject row = new JSONObject();
                row.put("name", sqlSetList.get(i).getName());
                row.put("description", sqlSetList.get(i).getDescription());
                jo.append("rows", row);
            }
            result = jo.toString();
            jo = null;
        } catch (Exception e) {
            e.printStackTrace();
            result = "{success: false, msg : 'ＪＳＯＮ转换发生异常'}";
        }
        return result;
    }

    public MySQLSetBean getMySQLSetBean(String name) {
        for (int i = 0, size = sqlSetList.size(); i < size; i++) {
            if (sqlSetList.get(i).getName().equals(name)) {
                return sqlSetList.get(i);
            }
        }
        return null;
    }

    public String change(String name, String description, String errorMsg,
            String nullMsg, String database, String fieldName,
            List<String> sqlList) {
        JSONObject jo = new JSONObject();
        try {
            if (MySQLSetDao.changeSQLSet(name, description, errorMsg, nullMsg,
                    database, fieldName, sqlList)) {
                MySQLSetBean bean = getMySQLSetBean(name);
                bean.setName(name);
                bean.setDatabase(database);
                bean.setDescription(description);
                bean.setErrorMsg(errorMsg);
                bean.setNullMsg(nullMsg);
                bean.setSqlList(sqlList);
                bean.setFieldName(fieldName);
                jo.put("success", true);
            } else {
                jo.put("success", false);
                jo.put("msg", "数据库异常");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return jo.toString();
    }

    public String add(String name, String description, String errorMsg,
            String nullMsg, String database, String fieldName,
            List<String> sqlList) {
        JSONObject jo = new JSONObject();
        try {
            if (getMySQLSetBean(name) == null) {
                MySQLSetBean bean = new MySQLSetBean();
                bean.setName(name);
                bean.setDatabase(database);
                bean.setDescription(description);
                bean.setErrorMsg(errorMsg);
                bean.setNullMsg(nullMsg);
                bean.setSqlList(sqlList);
                bean.setFieldName(fieldName);
                if (MySQLSetDao.addSQLSet(bean)) {
                    sqlSetList.add(bean);
                    jo.put("success", true);
                } else {
                    jo.put("success", false);
                    jo.put("msg", "数据库异常");
                }
            } else {
                jo.put("success", false);
                jo.put("msg", "名称已存在");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return jo.toString();
    }

    public String del(String name) {
        JSONObject jo = new JSONObject();
        try {

            MySQLSetBean bean = getMySQLSetBean(name);
            if (MySQLSetDao.delSQLSet(bean)) {
                sqlSetList.remove(bean);
                jo.put("success", true);
            } else {
                jo.put("success", false);
                jo.put("msg", "数据库异常");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return jo.toString();
    }

    public String look(String name) {
        JSONObject jo = new JSONObject();
        try {

            MySQLSetBean bean = getMySQLSetBean(name);
            if (bean != null) {
                JSONObject sqlSet = new JSONObject();
                sqlSet.put("name", bean.getName());
                sqlSet.put("description", bean.getDescription());
                sqlSet.put("database", bean.getDatabase());
                sqlSet.put("errorMsg", bean.getErrorMsg());
                sqlSet.put("nullMsg", bean.getNullMsg());
                sqlSet.put("fieldName", bean.getFieldName());
                JSONArray sql = new JSONArray();
                for (int i = 0, size = bean.getSqlList().size(); i < size; i++) {
                    sql.put(bean.getSqlList().get(i));
                }
                sqlSet.put("sql", sql);
                jo.put("sqlSet", sqlSet);
                jo.put("success", true);
            } else {
                jo.put("success", false);
                jo.put("msg", "数据库异常");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return jo.toString();
    }

    public String query(String[] args, String name) {
        MySQLSetBean bean = getMySQLSetBean(name);
        if (bean != null) {

            MySQLSetQueryDao dao = new MySQLSetQueryDao();
            dao.setSQLBean(bean);
            dao.setArgs(args);

            String operate = bean.getDatabase();

            if (OA_OPERATE.equals(operate)) {
                dao.setDataSetArray(FyConfig.getOA_DB_CONFIG());
            } else if (HR_OPERATE.equals(operate)) {
                dao.setDataSetArray(FyConfig.getEHR_DB_CONFIG());
            } else if (UAP_OPERATE.equals(operate)) {
                dao.setDataSetArray(FyConfig.getUAP_DB_CONFIG());
            } else if (ERP_OPERATE.equals(operate)) {
                dao.setDataSetArray(FyConfig.getERP_DB_CONFIG());
            }

            dao.query();
            bean = null;
            operate = null;

            return dao.getResult();
        } else {
            return "{success: false,msg:'未找到查询设置，请联系OA系统管理员'}";
        }
    }

    public List<List<String>> query_expExcel(String[] args, String name) {
        MySQLSetBean bean = getMySQLSetBean(name);
        if (bean != null) {

            MySQLSetQueryDao dao = new MySQLSetQueryDao();
            dao.setSQLBean(bean);
            dao.setArgs(args);

            String operate = bean.getDatabase();

            if (OA_OPERATE.equals(operate)) {
                dao.setDataSetArray(FyConfig.getOA_DB_CONFIG());
            } else if (HR_OPERATE.equals(operate)) {
                dao.setDataSetArray(FyConfig.getEHR_DB_CONFIG());
            } else if (UAP_OPERATE.equals(operate)) {
                dao.setDataSetArray(FyConfig.getUAP_DB_CONFIG());
            } else if (ERP_OPERATE.equals(operate)) {
                dao.setDataSetArray(FyConfig.getERP_DB_CONFIG());
            }

            return dao.query_expExcel();

        } 
        return null;
    }
}
